import mysql.connector
from mysql.connector import Error
import pandas as pd
import tkinter as tk
from tkinter import filedialog
from datetime import datetime, timedelta

def generate_time_intervals(initial_time_str, interval_delta, num_intervals):
    initial_time = datetime.strptime(initial_time_str, "%Y-%m-%d %H:%M:%S")
    return [
        (initial_time + i * interval_delta).strftime("%Y-%m-%d %H:%M:%S")
        for i in range(num_intervals + 1)
    ]

def build_query(time_list, device_ids, table_name):
    case_statements = []
    for i in range(len(time_list)-1):
        start = time_list[i]
        end = time_list[i+1]
        col_name = f"{start[11:16]}~{end[11:16]}"
        case = f"COUNT(CASE WHEN update_time >= '{start}' AND update_time < '{end}' THEN 1 ELSE NULL END) AS `{col_name}`"
        case_statements.append(case)
    
    placeholders = ', '.join(['%s'] * len(device_ids))
    query = f"""
    SELECT
        device_id,
        {', '.join(case_statements)}
    FROM
        {table_name}
    WHERE
        update_time >= %s
        AND update_time < %s
        AND device_id IN ({placeholders})
    GROUP BY
        device_id;
    """
    return query

def fetch_device_update_counts(start_time, table_name):
    try:
        connection = mysql.connector.connect(
            host="192.168.3.167",
            database="gwza231dev",
            user="tester",
            password="tester1234",
        )
        
        # 配置时间参数
        time_interval = timedelta(minutes=60)
        total_duration = timedelta(hours=1)
        num_intervals = total_duration // time_interval
        
        time_list = generate_time_intervals(start_time, time_interval, num_intervals)
        print(time_list)
        device_ids = ['GW1D94','GW511B','GW1721','GW45A8']
        end_time = time_list[-1]

        if connection.is_connected():
            cursor = connection.cursor(dictionary=True)
            query = build_query(time_list, device_ids, table_name)
            params = [start_time, end_time] + device_ids
            cursor.execute(query, params)
            return cursor.fetchall()

    except Error as e:
        print(f"MySQL Error: {e}")
        return None
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

def select_output_file_path():
    root = tk.Tk()
    root.withdraw()
    file_path = filedialog.asksaveasfilename(
        defaultextension=".csv",
        filetypes=[("CSV files", "*.csv"), ("All files", "*.*")]
    )
    return file_path

# 示例使用
start_time = '2025-03-28 15:35:00'
table_name = 'map_device_his0328'
results = fetch_device_update_counts(start_time, table_name)

for row in results:
    print(row)

df = pd.DataFrame(results)
csv_file_path = select_output_file_path()
df.to_csv(csv_file_path, index=False)